<%@ page contentType="text/html;charset=utf-8"%>
<%@ page import="java.util.Date"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<HTML><HEAD><TITLE>灵通邮件统计页面</TITLE>

<LINK href="Default.files/css.css" type=text/css rel=stylesheet>

<SCRIPT src="fPopUpCalendarDlg.js"></SCRIPT>

<SCRIPT language=javascript>
function check(){
<% 
String section="0";
String hostname=(String)session.getAttribute("mger_hostname");
 section=(String)session.getAttribute("mger_code");
%>
if((option.begintime.value==""))
	{
	alert("请输入查询时间");
	}
	
	else{
	option.submit();
	return true;
	}
	}



</SCRIPT>
</HEAD>
<BODY >
<%!


%>
<%
 String begintime=new SimpleDateFormat("yyyyMMdd").format(new Date());
 String city="0";

 if((request.getParameter("begintime")!=null)){
    begintime=request.getParameter("begintime");
    begintime=begintime.substring(0,4)+begintime.substring(5,7)+begintime.substring(8,10);
    }
  %>


<FORM name=option action="dateamount.jsp" method=post> 
   
<TABLE height="100%" cellSpacing=0 cellPadding=0 width="100%" border=0>
  <TBODY>
  <TR>
    <TD vAlign=top align="center" colSpan=2>
							<TABLE cellSpacing=0 cellPadding=0 width="100%" bgColor=#f1f1f1 border=0>
								<TBODY>
									<TR height=40>
										<TD>
											<FONT color="#00ff80">
												<FONT color="#8000ff">
													 灵通传真邮箱用户数日统计&nbsp;</FONT>
											</FONT>
										</TD>
										
										<TD align=right>
		
            
          选择会员类型
           
            <select name="user_type" class=9pt style="WIDTH: 70px">
                    <option value='10' <%if((request.getParameter("user_type")!=null)&&(request.getParameter("user_type").equals("10")))out.print("selected");%>><%out.print("(全部)");%></option>
                    <option value='PHS' <%if((request.getParameter("user_type")!=null)&&(request.getParameter("user_type").equals("PHS")))out.print("selected");%>><%out.print("小灵通");%></option>
                    <option value='PSTN' <%if((request.getParameter("user_type")!=null)&&(request.getParameter("user_type").equals("PSTN")))out.print("selected");%>><%out.print("固话");%></option>
                    <!-- option value='NET' <%if((request.getParameter("user_type")!=null)&&(request.getParameter("user_type").equals("NET")))out.print("selected");%>><%out.print("宽带");%></option-->
                    </select>
			查询日期
         <INPUT style="WIDTH: 70px" type="text" name=begintime value=<%if(request.getParameter("begintime")!=null)out.print(request.getParameter("begintime"));%>>
         <input type="button" name="button_begin" value="&gt;&gt;" onClick="fPopUpCalendarDlg(begintime);return false" >
                      <INPUT type=button name=tongji value="统计" onclick="return check()"></TD>
          
									</TR>
								</TBODY>
							</TABLE>
							<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0>
								<TBODY>
									<TR>
										<TD>
											<TABLE id=dgDownLoad style="BORDER-LEFT-COLOR: #cccccc; BORDER-BOTTOM-COLOR: #cccccc; WIDTH: 100%; BORDER-TOP-COLOR: #cccccc; BORDER-COLLAPSE: collapse; BORDER-RIGHT-COLOR: #cccccc" borderColor=#cccccc cellSpacing=0 rules=all border=1>
												<TBODY>
													<TR style="BACKGROUND-COLOR: #d0ebee" align="center">
													    <TD style="WIDTH: 5%">
															<FONT color="#ff0080">&nbsp;日期</FONT>
														</TD>
														<TD style="WIDTH: 5%">
															<FONT color="#ff0080">&nbsp;地区名</FONT>
														</TD>
														
														<TD style="WIDTH: 5%">
															<FONT color="#ff0080">正常普通用户


														</FONT></TD>
														<TD style="WIDTH: 5%">
															<FONT color="#ff0080">正常商务用户
														</FONT></TD>
														<TD style="WIDTH: 5%">
															<FONT color="#ff0080">正常精英用户
														</FONT></TD>
														<TD style="WIDTH: 5%">
															<FONT color="#ff0080">正常特惠用户
														</FONT></TD>
														<TD style="WIDTH: 5%" align=left>
															<FONT color="#ff0080">&nbsp;
															正常用户总数</FONT>
														</TD>
														<!-- TD style="WIDTH: 5%" align=left>
															<FONT color="#ff0080">暂停用户

															</FONT>
														</TD>
														<TD style="WIDTH: 5%" align=left>
															<FONT color="#ff0080">
														注销用户</FONT>
														</TD-->
														
														
		
													</TR>
												<%
												int putong=0;
												int shangwu=0;
												int jingying=0;
												int zanting=0;
												int zhuxiao=0;
												int xinzeng=0;
												int tehui=0;
											if(request.getParameter("today")==null){
												   String sql="";
												   //sql="select a.rq,b.areaname,a.zt_total,zx_total,mf_total,pt_total,sw_total,jy_total,th_total,xz_total from (select substring(createddate,1,8) rq,dbo.fn_getareacode(username) areacode,sum(case  when state=0 then 1 else 0 end ) zt_total,sum(case  when state=7 then 1 else 0 end ) zx_total,sum(case  when state=1 and grade=0 then 1 else 0 end) mf_total,sum(case  when state=1 and grade=1 then 1 else 0 end) pt_total,sum(case  when state=1 and grade=2 then 1 else 0 end) sw_total,sum(case  when state=1 and grade=3 then 1 else 0 end) jy_total,sum(case  when state=1 and grade=8 then 1 else 0 end) th_total,sum(case  when state=1 then 1 else 0 end) xz_total from  users a,service_conf b where a.grade=b.servid and  dbo.fn_getareacode(username)<>'' and domainName='10060.com' group by substring(createddate,1,8),dbo.fn_getareacode(username) )a,mz_areacode b where a.areacode=b.areacode and b.areaid=1 and a.rq='"+begintime+"' order by b.sortid";		
												    sql="select a.rq,b.areaname,a.zt_total,zx_total,mf_total,pt_total,sw_total,jy_total,th_total,xz_total from (select substring(createddate,1,8) rq,case when substring(username,1,3)='024' then substring(username,1,3) else substring(username,1,4) end areacode,sum(case  when state=0 then 1 else 0 end ) zt_total,sum(case  when state=7 then 1 else 0 end ) zx_total,sum(case  when state=1 and grade=0 then 1 else 0 end) mf_total,sum(case  when state=1 and grade=1 then 1 else 0 end) pt_total,sum(case  when state=1 and grade=2 then 1 else 0 end) sw_total,sum(case  when state=1 and grade=3 then 1 else 0 end) jy_total,sum(case  when state=1 and grade=8 then 1 else 0 end) th_total,sum(case  when state=1 then 1 else 0 end) xz_total from  users a where domainName='"+hostname+"' group by substring(createddate,1,8),case when substring(username,1,3)='024' then substring(username,1,3) else substring(username,1,4) end ) a, mz_areacode b where a.areacode=b.areacode and b.areaid=1 and a.rq='"+begintime+"' order by b.sortid";
												   if((request.getParameter("user_type")!=null)&&(!(request.getParameter("user_type").equals("10")))){
												   sql="select a.rq,b.areaname,a.zt_total,zx_total,mf_total,pt_total,sw_total,jy_total,th_total,xz_total from (select substring(createddate,1,8) rq,case when substring(username,1,3)='024' then substring(username,1,3) else substring(username,1,4) end areacode,sum(case  when state=0 then 1 else 0 end ) zt_total,sum(case  when state=7 then 1 else 0 end ) zx_total,sum(case  when state=1 and grade=0 then 1 else 0 end) mf_total,sum(case  when state=1 and grade=1 then 1 else 0 end) pt_total,sum(case  when state=1 and grade=2 then 1 else 0 end) sw_total,sum(case  when state=1 and grade=3 then 1 else 0 end) jy_total,sum(case  when state=1 and grade=8 then 1 else 0 end) th_total,sum(case  when state=1 then 1 else 0 end) xz_total from  users a where domainName='"+hostname+"' and user_type='"+request.getParameter("user_type")+"' group by substring(createddate,1,8),case when substring(username,1,3)='024' then substring(username,1,3) else substring(username,1,4) end ) a, mz_areacode b where a.areacode=b.areacode and b.areaid=1 and a.rq='"+begintime+"' order by b.sortid";
												   }
												    Context initCtx = new InitialContext();
												    Context envCtx = (Context) initCtx.lookup("java:comp/env");
												    DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");  
												     
												    Connection conn = ds.getConnection();
												    Statement stmt = conn.createStatement();
													ResultSet rst = stmt.executeQuery(sql);
													while(rst.next()){
													%>
												
													<TR  align="center">
													<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%
												                  out.print(begintime.substring(0,4)+"."+begintime.substring(4,6)+"."+begintime.substring(6,8));
												                  %>
																</FONT>
														
													</TD>
													<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%
                                                    out.print(rst.getString("areaname"));
																%>
																</FONT>
														
														</TD>
														
														<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%out.print(rst.getInt("pt_total"));
																putong+=rst.getInt("pt_total");
																%>
																</FONT>
														
														</TD>
														<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%out.print(rst.getInt("sw_total"));
																shangwu+=rst.getInt("sw_total");
																%>
																</FONT>
														
														</TD>
														<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%out.print(rst.getInt("jy_total"));
																jingying+=rst.getInt("jy_total");
																%>
																</FONT>
														
														</TD>
														<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%out.print(rst.getInt("th_total"));
																tehui+=rst.getInt("th_total");
																%>
																</FONT>
														
														</TD>
														<TD style="WIDTH: 5%" align=left>
															<FONT >
																
																<FONT color="#8080ff">
																	<%out.print(rst.getInt("xz_total"));
																xinzeng+=rst.getInt("xz_total");
																%>
																</FONT>
															</FONT>
														</TD>
														
														<!-- TD style="WIDTH: 5%" align=left>
															
																
																<FONT color="#8080ff">
																	<%out.print(rst.getInt("zt_total"));
																zanting+=rst.getInt("zt_total");
																%>
																</FONT>
														</TD>
														<TD style="WIDTH: 5%" align=left>
															<FONT >
																
																<FONT color="#8080ff">
																	<%out.print(rst.getInt("zx_total"));
																zhuxiao+=rst.getInt("zx_total");
																%>
																</FONT>
															</FONT>
														</TD-->
													
														
														
														
													</TR>
													<%}
													
													rst.close();
													stmt.close();
													conn.close();
													%><TR  align="center">
													<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%
												                  out.print(begintime.substring(0,4)+"."+begintime.substring(4,6)+"."+begintime.substring(6,8));
						
												                  %>
																</FONT>
														
													</TD>
													<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%
												                  out.print("合计");
												                  %>
																</FONT>
														
													</TD>
													
														
														<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%out.print(putong);%>
																</FONT>
														
														</TD>
														<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%out.print(shangwu);%>
																</FONT>
														
														</TD>
														<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%out.print(jingying);%>
																</FONT>
														
														</TD>
														<TD style="WIDTH: 5%" align=left>
																<FONT color="#8080ff">
																<%out.print(tehui);%>
																</FONT>
														
														</TD>
														<TD style="WIDTH: 5%" align=left>
															<FONT >
																
																<FONT color="#8080ff">
																	<%out.print(xinzeng);%>
																</FONT>
															</FONT>
														</TD>
														
														<!-- TD style="WIDTH: 5%" align=left>
															
																
																<FONT color="#8080ff">
																	<%out.print(zanting);%>
																</FONT>
													  </TD>
														<TD style="WIDTH: 5%" align=left>
															<FONT >
																
																<FONT color="#8080ff">
																	<%out.print(zhuxiao);%>
																</FONT>
															</FONT>
														</TD-->
													
														
														
													</TR><%
												}
												
												%>

												</TBODY>
											</TABLE>
										</TD>
									</TR>
								</TBODY>
							</TABLE>
						</TD></TR>
  <TR>
    <TD height=20>
      <TABLE cellSpacing=0 cellPadding=0 width="100%" align=center border=0>
        <TBODY>
        <TR>
          <!-- TD width=180><IMG height=40 
            src="Default.files/main_bottom_pic01.gif" width=180></TD-->
          <TD width="40%" background=Default.files/main_bottom_bg.gif></TD>
          <TD align=right background=Default.files/main_bottom_bg.gif>
            <TABLE style="FONT-SIZE: smaller; FONT-FAMILY: 宋体" cellSpacing=0 
            cellPadding=0 width="100%" align=center border=0>
              <TBODY>
              <TR>
                <!-- TD noWrap>共有<FONT style="COLOR: red"></FONT>条记录&nbsp;</TD-->
                <TD noWrap>当前为<FONT 
                  style="COLOR: red; FONT-FAMILY: 宋体"><%if(request.getParameter("today")==null)out.print("1");else out.print("0");%></FONT>/<FONT 
                  style="COLOR: red; FONT-FAMILY: 宋体"><%if(request.getParameter("today")==null)out.print("1");else out.print("0");%></FONT>页</TD>
                <TD noWrap align=right><INPUT style="BORDER-RIGHT: blue 1px; BORDER-TOP: blue 1px; FONT-SIZE: 12pt; BORDER-LEFT: blue 1px; CLIP: rect(auto auto auto auto); CURSOR: hand; COLOR: blue; BORDER-BOTTOM: blue 1px; FONT-FAMILY: Webdings; HEIGHT: 20px; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" disabled onclick="__doPostBack('pageHelper','first')" type=button value=9><INPUT style="BORDER-RIGHT: blue 1px; BORDER-TOP: blue 1px; FONT-SIZE: 12pt; BORDER-LEFT: blue 1px; CLIP: rect(auto auto auto auto); CURSOR: hand; COLOR: blue; BORDER-BOTTOM: blue 1px; FONT-FAMILY: Webdings; HEIGHT: 20px; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" disabled onclick="__doPostBack('pageHelper','prev')" type=button value=3><INPUT style="BORDER-RIGHT: blue 1px; BORDER-TOP: blue 1px; FONT-SIZE: 12pt; BORDER-LEFT: blue 1px; CLIP: rect(auto auto auto auto); CURSOR: hand; COLOR: blue; BORDER-BOTTOM: blue 1px; FONT-FAMILY: Webdings; HEIGHT: 20px; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" disabled onclick="__doPostBack('pageHelper','next')" type=button value=4><INPUT style="BORDER-RIGHT: blue 1px; BORDER-TOP: blue 1px; FONT-SIZE: 12pt; BORDER-LEFT: blue 1px; CLIP: rect(auto auto auto auto); CURSOR: hand; COLOR: blue; BORDER-BOTTOM: blue 1px; FONT-FAMILY: Webdings; HEIGHT: 20px; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" disabled onclick="__doPostBack('pageHelper','last')" type=button value=:>
               </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></FORM></BODY></HTML>
